package com.lw.lost.db

import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import com.lw.lost.daos.*

class DBlost(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

    val userDao: UserDao by lazy { UserDao(this) }
    val interestDao: InterestDao by lazy { InterestDao(writableDatabase) }
    val matchConditionDao: MatchConditionDao by lazy { MatchConditionDao(writableDatabase) }
    val matchRecordDao: MatchRecordDao by lazy { MatchRecordDao(writableDatabase) }
    val messageDao: MessageDao by lazy { MessageDao(writableDatabase) }
    val postDao: PostDao by lazy { PostDao(writableDatabase) }
    val reportDao: ReportDao by lazy { ReportDao(writableDatabase) }
    val feedbackDao: FeedbackDao by lazy { FeedbackDao(writableDatabase) }
    val friendRequestDao: FriendRequestDao by lazy { FriendRequestDao(writableDatabase) }

    companion object {
        private const val DATABASE_NAME = "lost.db"
        private const val DATABASE_VERSION = 8
    }

    override fun onCreate(db: SQLiteDatabase) {
        // 创建用��户表
        db.execSQL("""
            CREATE TABLE IF NOT EXISTS users (
                userId INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT NOT NULL UNIQUE,
                password TEXT NOT NULL,
                name TEXT NOT NULL,
                phone TEXT NOT NULL,
                status INTEGER NOT NULL DEFAULT 1,
                gender INTEGER NOT NULL,
                age INTEGER NOT NULL,
                avatar TEXT
            )
        """)

        // 创建兴趣表
        db.execSQL("""
            CREATE TABLE IF NOT EXISTS interests (
                interestId INTEGER PRIMARY KEY AUTOINCREMENT,
                userId INTEGER NOT NULL,
                education TEXT NOT NULL,
                content TEXT NOT NULL,
                createTime DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (userId) REFERENCES users(userId)
            )
        """)

        // 创建匹配条件表
        db.execSQL("""
            CREATE TABLE IF NOT EXISTS match_conditions (
                conditionId INTEGER PRIMARY KEY AUTOINCREMENT,
                userId INTEGER NOT NULL,
                interestId INTEGER NOT NULL,
                preferGender INTEGER NOT NULL,
                minAge INTEGER NOT NULL,
                maxAge INTEGER NOT NULL,
                createTime DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (userId) REFERENCES users(userId),
                FOREIGN KEY (interestId) REFERENCES interests(interestId)
            )
        """)

        // 创建匹配记录表
        db.execSQL("""
            CREATE TABLE IF NOT EXISTS match_records (
                recordId INTEGER PRIMARY KEY AUTOINCREMENT,
                userId INTEGER NOT NULL,
                matchedUserId INTEGER NOT NULL,
                matchTime DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (userId) REFERENCES users(userId),
                FOREIGN KEY (matchedUserId) REFERENCES users(userId)
            )
        """)

        // 创建消息表
        db.execSQL("""
            CREATE TABLE messages (
                messageId INTEGER PRIMARY KEY AUTOINCREMENT,
                senderId INTEGER NOT NULL,
                receiverId INTEGER NOT NULL,
                content TEXT NOT NULL,
                imageUrl TEXT,
                sendTime DATETIME DEFAULT CURRENT_TIMESTAMP,
                isRead INTEGER DEFAULT 0,
                FOREIGN KEY (senderId) REFERENCES users(userId),
                FOREIGN KEY (receiverId) REFERENCES users(userId)
            )
        """)

        // 创建帖子表
        db.execSQL("""
            CREATE TABLE IF NOT EXISTS posts (
                postId INTEGER PRIMARY KEY AUTOINCREMENT,
                userId INTEGER NOT NULL,
                content TEXT NOT NULL,
                imageUrl TEXT,
                status INTEGER NOT NULL DEFAULT 0,
                createTime TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updateTime TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                likeCount INTEGER NOT NULL DEFAULT 0,
                commentCount INTEGER NOT NULL DEFAULT 0,
                FOREIGN KEY (userId) REFERENCES users(userId)
            )
        """)

        // 创建举报表
        db.execSQL("""
            CREATE TABLE reports (
                reportId INTEGER PRIMARY KEY AUTOINCREMENT,
                userId INTEGER NOT NULL,
                targetId INTEGER NOT NULL,
                type TEXT NOT NULL,
                reason TEXT NOT NULL,
                status INTEGER NOT NULL DEFAULT 0,
                reportTime INTEGER NOT NULL,
                FOREIGN KEY (userId) REFERENCES users(userId),
                FOREIGN KEY (targetId) REFERENCES users(userId)
            )
        """)

        // 创建反馈表
        db.execSQL("""
            CREATE TABLE IF NOT EXISTS feedbacks (
                feedbackId INTEGER PRIMARY KEY AUTOINCREMENT,
                userId INTEGER NOT NULL,
                content TEXT NOT NULL,
                status INTEGER NOT NULL DEFAULT 0,
                feedbackTime DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (userId) REFERENCES users(userId)
            )
        """)

        // 添加点赞记录表
        db.execSQL("""
            CREATE TABLE IF NOT EXISTS post_likes (
                likeId INTEGER PRIMARY KEY AUTOINCREMENT,
                postId INTEGER NOT NULL,
                userId INTEGER NOT NULL,
                createTime TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (postId) REFERENCES posts(postId),
                FOREIGN KEY (userId) REFERENCES users(userId),
                UNIQUE(postId, userId)
            )
        """)

        // 添加评论表
        db.execSQL("""
            CREATE TABLE IF NOT EXISTS post_comments (
                commentId INTEGER PRIMARY KEY AUTOINCREMENT,
                postId INTEGER NOT NULL,
                userId INTEGER NOT NULL,
                content TEXT NOT NULL,
                createTime TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (postId) REFERENCES posts(postId),
                FOREIGN KEY (userId) REFERENCES users(userId)
            )
        """)

        // 添加好友验证请求表
        db.execSQL("""
            CREATE TABLE IF NOT EXISTS friend_requests (
                requestId INTEGER PRIMARY KEY AUTOINCREMENT,
                senderId INTEGER NOT NULL,
                receiverId INTEGER NOT NULL,
                message TEXT,
                status INTEGER NOT NULL DEFAULT 0, -- 0: 待处理, 1: 已同意, 2: 已拒绝
                createTime TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (senderId) REFERENCES users(userId),
                FOREIGN KEY (receiverId) REFERENCES users(userId)
            )
        """)

        // 添加初始数据
        initializeData(db)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        if (oldVersion < 3) {
            // 为users表添加avatar列
            db.execSQL("ALTER TABLE users ADD COLUMN avatar TEXT")
            db.execSQL("ALTER TABLE posts ADD COLUMN likeCount INTEGER NOT NULL DEFAULT 0")
            db.execSQL("ALTER TABLE posts ADD COLUMN commentCount INTEGER NOT NULL DEFAULT 0")
        }
        
        if (oldVersion < 4) {
            // 创建点赞记录表
            db.execSQL("""
                CREATE TABLE IF NOT EXISTS post_likes (
                    likeId INTEGER PRIMARY KEY AUTOINCREMENT,
                    postId INTEGER NOT NULL,
                    userId INTEGER NOT NULL,
                    createTime TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (postId) REFERENCES posts(postId),
                    FOREIGN KEY (userId) REFERENCES users(userId),
                    UNIQUE(postId, userId)
                )
            """)
        }

        if (oldVersion < 5) {
            // 创建评论表
            db.execSQL("""
                CREATE TABLE IF NOT EXISTS post_comments (
                    commentId INTEGER PRIMARY KEY AUTOINCREMENT,
                    postId INTEGER NOT NULL,
                    userId INTEGER NOT NULL,
                    content TEXT NOT NULL,
                    createTime TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (postId) REFERENCES posts(postId),
                    FOREIGN KEY (userId) REFERENCES users(userId)
                )
            """)
        }

        if (oldVersion < 6) {
            // 创建好友请求表
            db.execSQL("""
                CREATE TABLE IF NOT EXISTS friend_requests (
                    requestId INTEGER PRIMARY KEY AUTOINCREMENT,
                    senderId INTEGER NOT NULL,
                    receiverId INTEGER NOT NULL,
                    message TEXT,
                    status INTEGER NOT NULL DEFAULT 0,
                    createTime TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (senderId) REFERENCES users(userId),
                    FOREIGN KEY (receiverId) REFERENCES users(userId)
                )
            """)
        }

//        if (oldVersion < 7) {
//            // 创建消息记录表
//            db.execSQL("""
//                CREATE TABLE IF NOT EXISTS messages (
//                    messageId INTEGER PRIMARY KEY AUTOINCREMENT,
//                    senderId INTEGER NOT NULL,
//                    receiverId INTEGER NOT NULL,
//                    content TEXT NOT NULL,
//                    sendTime TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
//                    FOREIGN KEY (senderId) REFERENCES users(userId),
//                    FOREIGN KEY (receiverId) REFERENCES users(userId)
//                )
//            """)
//        }
    }

    private fun initializeData(db: SQLiteDatabase) {
        // 添加用户数据
        db.execSQL("""
            INSERT INTO users (username, password, name, phone, status, gender, age, avatar) VALUES 
            ('admin', 'admin123', '超级管理员', '13800000000', 2, 1, 30, null),
            
            -- 男性用户（10人）
            ('user1', '123456', '张三', '13811111111', 1, 0, 25, null),
            ('user2', '123456', '李四', '13822222222', 1, 0, 28, null),
            ('user3', '123456', '王五', '13833333333', 1, 0, 22, null),
            ('user4', '123456', '赵六', '13844444444', 1, 0, 27, null),
            ('user5', '123456', '孙七', '13855555555', 1, 0, 24, null),
            ('user6', '123456', '周八', '13866666666', 1, 0, 29, null),
            ('user7', '123456', '吴九', '13877777777', 1, 0, 26, null),
            ('user8', '123456', '郑十', '13888888888', 1, 0, 23, null),
            ('user9', '123456', '陈一', '13899999999', 1, 0, 31, null),
            ('user10', '123456', '杨二', '13800000001', 1, 0, 30, null),
            
            -- 女性用户（10人）
            ('user11', '123456', '刘梅', '13800000002', 1, 1, 24, null),
            ('user12', '123456', '张婷', '13800000003', 1, 1, 26, null),
            ('user13', '123456', '王���', '13800000004', 1, 1, 23, null),
            ('user14', '123456', '李玲', '13800000005', 1, 1, 27, null),
            ('user15', '123456', '赵雪', '13800000006', 1, 1, 25, null),
            ('user16', '123456', '孙艳', '13800000007', 1, 1, 28, null),
            ('user17', '123456', '周琳', '13800000008', 1, 1, 22, null),
            ('user18', '123456', '吴娜', '13800000009', 1, 1, 29, null),
            ('user19', '123456', '郑红', '13800000010', 1, 1, 24, null),
            ('user20', '123456', '陈静', '13800000011', 1, 1, 26, null),
            
            -- 已封禁用户
            ('blocked1', '123456', '封禁用户1', '13800000012', 0, 1, 24, null)
        """)

        // 添加兴趣数据
        db.execSQL("""
            INSERT INTO interests (userId, education, content) VALUES 
            -- 男性用户兴趣
            (2, '本科', '喜欢篮球，阅读，旅行'),
            (3, '研究生', '喜欢足球，摄影，音乐'),
            (4, '大专', '喜欢游泳，美食，电影'),
            (5, '本科', '喜欢���身，烹饪，摄影'),
            (6, '研究生', '喜欢网球，绘画，旅行'),
            (7, '本科', '喜欢跑步，吉他，电影'),
            (8, '大专', '喜欢滑板，摄影，音乐'),
            (9, '研究生', '喜欢羽毛球，阅读，美食'),
            (10, '本科', '喜欢游泳，旅行，摄影'),
            (11, '大专', '喜欢篮球，电影，音乐'),
            
            -- 女性用户兴趣
            (12, '本科', '喜欢瑜伽，绘画，摄影'),
            (13, '研究生', '喜欢舞蹈，美食，旅行'),
            (14, '大专', '喜欢钢琴，阅读，电影'),
            (15, '本科', '喜欢游泳，烹饪，音乐'),
            (16, '研究生', '喜欢瑜伽，摄影，美食'),
            (17, '本科', '喜欢跳舞，步行，绘画'),
            (18, '大专', '喜欢钢琴，美食，电影'),
            (19, '研究生', '喜欢瑜伽，阅读，摄影'),
            (20, '本科', '喜欢舞蹈，烹饪，音乐'),
            (21, '大专', '喜欢游泳，旅行，美食')
        """)

        // 添加匹配条件数据
        db.execSQL("""
            INSERT INTO match_conditions (userId, interestId, preferGender, minAge, maxAge) VALUES 
            -- 男性用户匹配条件
            (2, 1, 1, 20, 28),
            (3, 2, 1, 22, 30),
            (4, 3, 1, 20, 27),
            (5, 4, 1, 21, 29),
            (6, 5, 1, 22, 28),
            (7, 6, 1, 20, 26),
            (8, 7, 1, 21, 27),
            (9, 8, 1, 22, 29),
            (10, 9, 1, 20, 28),
            (11, 10, 1, 21, 30),
            
            -- 女性用户匹配条件
            (12, 11, 0, 23, 32),
            (13, 12, 0, 25, 33),
            (14, 13, 0, 24, 31),
            (15, 14, 0, 25, 32),
            (16, 15, 0, 26, 33),
            (17, 16, 0, 24, 30),
            (18, 17, 0, 25, 32),
            (19, 18, 0, 26, 33),
            (20, 19, 0, 24, 31),
            (21, 20, 0, 25, 32)
        """)

        // 添加帖子数据
        db.execSQL("""
            INSERT INTO posts (userId, content) VALUES 
            (3, '今天天气真好'),
            (4, '分享一首好歌'),
            (5, '推荐一部电影')
        """)

        // 添加举报数据
        db.execSQL("""
            INSERT INTO reports (userId, targetId, type, reason, status, reportTime) VALUES 
            (3, 4, 'user', '该用户发布不当言论', 0, ${System.currentTimeMillis()}),
            (4, 5, 'user', '该用户存在广告行为', 0, ${System.currentTimeMillis()})
        """)

        // 添加反馈数据
        db.execSQL("""
            INSERT INTO feedbacks (userId, content) VALUES 
            (3, '希望增加更多功能'),
            (4, '界面很好用')
        """)
    }
}